﻿Imports System.Data.SqlClient

Public Class frmRTarjetones

    Private Sub frmRTarjetones_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        radioAltas.Checked = True
        datBaja1.Value = Now()
        datBaja2.Value = Now()
    End Sub
    Sub Todos()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim strSql As String = ""

        strSql = "select E.IDEmpleado,E.Codigo,Nombrecompleto,Tarjeton, Fechaexpira,EstadoEmpleado,descripcion"
        strSql = strSql & " from Empleados E INNER JOIN Capacitacion T "
        strSql = strSql & " ON E.IDEmpleado=T.IDEmpleado INNER JOIN Departamentos D ON e.IdDepartamento=d.IdDepartamento and STATUS='ALTA' and EstadoEmpleado<>'B'"
        strSql = strSql & " order by FECHAEXPIRA"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader




        wb.SHEETS(1).cells(rng, 1).value = "Clave"
        wb.SHEETS(1).cells(rng, 2).value = "Nombre del Empleado"
        wb.SHEETS(1).cells(rng, 3).value = "Departamento"
        wb.SHEETS(1).cells(rng, 4).value = "Status"
        wb.SHEETS(1).cells(rng, 5).value = "Tarjeton"
        wb.SHEETS(1).cells(rng, 6).value = "Vencimiento"
        Dim i As Integer
        For i = 1 To 6
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).font.size = 9
            wb.SHEETS(1).cells(rng, i).interior.colorindex = 1
            wb.SHEETS(1).cells(rng, i).font.colorindex = 2
        Next

        Dim cont As Integer = 0
        Dim Vigencia As Date
        Dim strFechaMov As String = ""
        Dim status As String = ""

        rng = rng + 1

        Do While rdBuscar.Read()
            cont = cont + 1
            status = ""
            wb.SHEETS(1).cells(rng, 1).value = rdBuscar("Codigo").ToString.Trim
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Nombrecompleto").ToString.Trim
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("Descripcion").ToString.Trim

            status = rdBuscar("EstadoEmpleado").ToString.Trim
            Select Case status
                Case "A"
                    status = "Alta"
                Case "B"
                    status = "Baja"
                Case "R"
                    status = "Reingreso"
            End Select

            wb.SHEETS(1).cells(rng, 4).value = status
            Vigencia = rdBuscar("fechaexpira")
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("Tarjeton").ToString.Trim
            wb.SHEETS(1).cells(rng, 6).value = Vigencia.Day & "/" & Vigencia.Month & "/" & Vigencia.Year
            For i = 1 To 6
                wb.SHEETS(1).cells(rng, i).font.size = 9
            Next
            rng = rng + 1

        Loop

        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 30
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 12

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Informe de Tarjetones. Filtro: Todos"
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & usuarioactivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8
    End Sub
    Sub Vencimiento()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection
        Dim strFecha1 As String = Format(Me.datBaja1.Value.Year, "0000") & Format(Me.datBaja1.Value.Month, "00") & Format(Me.datBaja1.Value.Day, "00")
        Dim strFecha2 As String = Format(Me.datBaja2.Value.Year, "0000") & Format(Me.datBaja2.Value.Month, "00") & Format(Me.datBaja2.Value.Day, "00")

        Dim strSql As String = ""

        strSql = "select E.IDEmpleado,E.Codigo,Nombrecompleto,Tarjeton, Fechaexpira,EstadoEmpleado,descripcion"
        strSql = strSql & " from Empleados E INNER JOIN Capacitacion T "
        strSql = strSql & " ON E.IDEmpleado=T.IDEmpleado INNER JOIN Departamentos D ON e.IdDepartamento=d.IdDepartamento and STATUS='ALTA' and EstadoEmpleado<>'B'"
        strSql = strSql & " order by FECHAEXPIRA"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader




        wb.SHEETS(1).cells(rng, 1).value = "Clave"
        wb.SHEETS(1).cells(rng, 2).value = "Nombre del Empleado"
        wb.SHEETS(1).cells(rng, 3).value = "Departamento"
        wb.SHEETS(1).cells(rng, 4).value = "Status"
        wb.SHEETS(1).cells(rng, 5).value = "Tarjeton"
        wb.SHEETS(1).cells(rng, 6).value = "Vencimiento"
        Dim i As Integer
        For i = 1 To 6
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).font.size = 9
            wb.SHEETS(1).cells(rng, i).interior.colorindex = 1
            wb.SHEETS(1).cells(rng, i).font.colorindex = 2
        Next

        Dim cont As Integer = 0
        Dim Vigencia As Date
        Dim strFechaMov As String = ""
        Dim status As String = ""

        rng = rng + 1

        Do While rdBuscar.Read()
            strFechaMov = Format(CDate(rdBuscar("fechaexpira").ToString).Year, "0000") & Format(CDate(rdBuscar("fechaexpira").ToString).Month, "00") & Format(CDate(rdBuscar("fechaexpira").ToString).Day, "00")
            If strFechaMov >= strFecha1 And strFechaMov <= strFecha2 Then
                cont = cont + 1
                status = ""
                wb.SHEETS(1).cells(rng, 1).value = rdBuscar("Codigo").ToString.Trim
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Nombrecompleto").ToString.Trim
                wb.SHEETS(1).cells(rng, 3).value = rdBuscar("descripcion").ToString.Trim
                status = rdBuscar("EstadoEmpleado").ToString.Trim
                Select Case status
                    Case "A"
                        status = "Alta"
                    Case "B"
                        status = "Baja"
                    Case "R"
                        status = "Reingreso"
                End Select

                wb.SHEETS(1).cells(rng, 4).value = status
                Vigencia = rdBuscar("fechaexpira")
                wb.SHEETS(1).cells(rng, 5).value = rdBuscar("Tarjeton").ToString.Trim
                wb.SHEETS(1).cells(rng, 6).value = Vigencia.Day & "/" & Vigencia.Month & "/" & Vigencia.Year
                For i = 1 To 6
                    wb.SHEETS(1).cells(rng, i).font.size = 9
                Next
                rng = rng + 1
            End If
        Loop

        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 30
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 12

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Informe de Tarjetones. Filtro: Fecha de Vencimiento:  Del " & Me.datBaja1.Value.Day & "/" & Me.datBaja1.Value.Month & "/" & Me.datBaja1.Value.Year & " al " & Me.datBaja2.Value.Day & "/" & Me.datBaja2.Value.Month & "/" & Me.datBaja2.Value.Year
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & usuarioactivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub

    Private Sub radioAltas_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radioAltas.CheckedChanged
        If radioAltas.Checked = True Then
            datBaja1.Enabled = False
            datBaja2.Enabled = False
        Else
            datBaja1.Enabled = True
            datBaja2.Enabled = True
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub btnAbrir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub RadButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub

    Private Sub ToolStripButton2_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton2.Click
        If radioAltas.Checked = True Then
            Todos()
        Else
            Vencimiento()
        End If
    End Sub

    Private Sub ToolStripButton1_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton1.Click
        Close()

    End Sub
End Class